﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_Admin.cs
* 创建标识：曾乃强 15158756652 2011-03-14 03:03:50
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;


namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_Admin*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_Admin
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_Admin obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "Admin ([AdminName],[AdminPass],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText],[RoleID],[LoginNum])");
            sql.Append("values(@AdminName,@AdminPass,@IsLock,@IsAdmin,@DepartmentName,@AdminNo,@AdminText,@RoleID,@LoginNum)");
            
            SqlParameter[] pra = {
                            new SqlParameter("@AdminName",obj.AdminName),
							new SqlParameter("@AdminPass",obj.AdminPass),
							new SqlParameter("@IsLock",obj.IsLock),
							new SqlParameter("@IsAdmin",obj.IsAdmin),
							new SqlParameter("@DepartmentName",obj.DepartmentName),
							new SqlParameter("@AdminNo",obj.AdminNo),
							new SqlParameter("@AdminText",obj.AdminText),
							new SqlParameter("@RoleID",obj.RoleID),
                            new SqlParameter("@LoginNum",obj.LoginNum)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Update(Model.CNVP_Admin obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Admin set [AdminName] = @AdminName,");
             if (obj.AdminPass != "D41D8CD98F00B204E9800998ECF8427E")//空加密值
             {
                 sql.Append(" [AdminPass] = @AdminPass, ");
             }
             sql.Append(" [IsLock] = @IsLock,[IsAdmin] = @IsAdmin,[DepartmentName] = @DepartmentName,[AdminNo] = @AdminNo,[AdminText] = @AdminText,[RoleID] = @RoleID where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID==0?DBNull.Value:(object)obj.ID),
							new SqlParameter("@AdminName",obj.AdminName),
                            new SqlParameter("@AdminPass",obj.AdminPass),
							new SqlParameter("@IsLock",obj.IsLock),
							new SqlParameter("@IsAdmin",obj.IsAdmin),
							new SqlParameter("@DepartmentName",obj.DepartmentName),
							new SqlParameter("@AdminNo",obj.AdminNo),
							new SqlParameter("@AdminText",obj.AdminText),
							new SqlParameter("@RoleID",obj.RoleID)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public int Delete(Model.CNVP_Admin obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "Admin where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public List<Model.CNVP_Admin> LoadAll()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[AdminName],[AdminPass],[LastLoginIp],[LastLoginTime],[LoginNum],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText],[RoleID] from " + Prefix + "Admin");
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Admin> list=new List<Model.CNVP_Admin>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Admin cnvp_admin = new Model.CNVP_Admin();
                    cnvp_admin.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_admin.AdminName = dr["AdminName"] is DBNull ? String.Empty : (string)dr["AdminName"];
                    cnvp_admin.AdminPass = dr["AdminPass"] is DBNull ? String.Empty : (string)dr["AdminPass"];
                    cnvp_admin.LastLoginIp = dr["LastLoginIp"] is DBNull ? String.Empty : (string)dr["LastLoginIp"];
                    cnvp_admin.LastLoginTime = dr["LastLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastLoginTime"];
                    cnvp_admin.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                    cnvp_admin.IsLock = dr["IsLock"] is DBNull ? (byte)0 : (byte)dr["IsLock"];
                    cnvp_admin.IsAdmin = dr["IsAdmin"] is DBNull ? (byte)0 : (byte)dr["IsAdmin"];
                    cnvp_admin.DepartmentName = dr["DepartmentName"] is DBNull ? String.Empty : (string)dr["DepartmentName"];
                    cnvp_admin.AdminNo = dr["AdminNo"] is DBNull ? String.Empty : (string)dr["AdminNo"];
                    cnvp_admin.AdminText = dr["AdminText"] is DBNull ? String.Empty : (string)dr["AdminText"];
                    cnvp_admin.RoleID = dr["RoleID"] is DBNull ? 0 : (int)dr["RoleID"];
                    list.Add(cnvp_admin);
                }
            }
            return list;

        }

        public Model.CNVP_Admin LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [AdminName],[AdminPass],[LastLoginIp],[LastLoginTime],[LoginNum],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText],[RoleID] from " + Prefix + "Admin");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra); 
            Model.CNVP_Admin cnvp_admin = new Model.CNVP_Admin();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_admin.AdminName = dr["AdminName"] is DBNull ? String.Empty : (string)dr["AdminName"];
                cnvp_admin.AdminPass = dr["AdminPass"] is DBNull ? String.Empty : (string)dr["AdminPass"];
                cnvp_admin.LastLoginIp = dr["LastLoginIp"] is DBNull ? String.Empty : (string)dr["LastLoginIp"];
                cnvp_admin.LastLoginTime = dr["LastLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastLoginTime"];
                cnvp_admin.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                cnvp_admin.IsLock = dr["IsLock"] is DBNull ? (byte)0 : (byte)dr["IsLock"];
                cnvp_admin.IsAdmin = dr["IsAdmin"] is DBNull ? (byte)0 : (byte)dr["IsAdmin"];
                cnvp_admin.DepartmentName = dr["DepartmentName"] is DBNull ? String.Empty : (string)dr["DepartmentName"];
                cnvp_admin.AdminNo = dr["AdminNo"] is DBNull ? String.Empty : (string)dr["AdminNo"];
                cnvp_admin.AdminText = dr["AdminText"] is DBNull ? String.Empty : (string)dr["AdminText"];
                cnvp_admin.RoleID = dr["RoleID"] is DBNull ? 0 : (int)dr["RoleID"];
            }
            return cnvp_admin;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Model.CNVP_Admin cust_LoginByName(Model.CNVP_Admin obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 ID,RoleID from " + Prefix + "Admin");
            sql.Append(" where [AdminName]=@AdminName AND [AdminPass]=@AdminPass AND [IsLock]=@IsLock");
            SqlParameter[] pra = { 
							new SqlParameter("@AdminName",obj.AdminName),
							new SqlParameter("@AdminPass",obj.AdminPass),
                            new SqlParameter("@IsLock",obj.IsLock)
                                  };
            DataTable dt = DbHelper.ExecuteTable(sql.ToString(), pra);
            if (dt != null && dt.Rows.Count > 0)
            {
                obj.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
                obj.RoleID = Convert.ToInt32(dt.Rows[0]["RoleID"]);
                return obj;
            }
            else
            {
                return null;
            }
        }

        public int cust_UpdateLogin(Model.CNVP_Admin obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Admin set [LastLoginIp] = @LastLoginIp,[LastLoginTime] = @LastLoginTime,[LoginNum] = [LoginNum]+1 where [AdminName]=@AdminName AND [AdminPass]=@AdminPass AND [IsLock]=@IsLock");

            SqlParameter[] pra = { 
							new SqlParameter("@AdminName",obj.AdminName),
							new SqlParameter("@AdminPass",obj.AdminPass),
							new SqlParameter("@LastLoginIp",obj.LastLoginIp),
							new SqlParameter("@LastLoginTime",obj.LastLoginTime),
							new SqlParameter("@IsLock",obj.IsLock)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        #endregion

        public DataTable GetPage(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string AllFields = "a.ID, a.AdminName, a.AdminPass, a.LastLoginIp, a.LastLoginTime, a.LoginNum, a.IsLock, a.IsAdmin, a.DepartmentName, a.AdminNo, a.AdminText, a.RoleID,b.RoleName";
                string Condition = "" + Prefix + "Admin as a left join " + Prefix + "AdminRole as b on a.RoleID=b.ID";
                string IndexField = "a.ID";
                string OrderFields = "order by a.Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        public List<Model.CNVP_Admin> QurryListFK_CNVP_AdminRole_REFERENCE_CNVP_Admin(int RoleID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select[ID],[AdminName],[AdminPass],[LastLoginIp],[LastLoginTime],[LoginNum],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText] from " + Prefix + "Admin WHERE [RoleID]=@RoleID");

            SqlParameter[] pra ={
                               new SqlParameter("@RoleID",RoleID)
                               };

            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_Admin> list = new List<Model.CNVP_Admin>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Admin cnvp_admin = new Model.CNVP_Admin();
                    cnvp_admin.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_admin.AdminName = dr["AdminName"] is DBNull ? String.Empty : (string)dr["AdminName"];
                    cnvp_admin.AdminPass = dr["AdminPass"] is DBNull ? String.Empty : (string)dr["AdminPass"];
                    cnvp_admin.LastLoginIp = dr["LastLoginIp"] is DBNull ? String.Empty : (string)dr["LastLoginIp"];
                    cnvp_admin.LastLoginTime = dr["LastLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastLoginTime"];
                    cnvp_admin.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                    cnvp_admin.IsLock = dr["IsLock"] is DBNull ? (byte)0 : (byte)dr["IsLock"];
                    cnvp_admin.IsAdmin = dr["IsAdmin"] is DBNull ? (byte)0 : (byte)dr["IsAdmin"];
                    cnvp_admin.DepartmentName = dr["DepartmentName"] is DBNull ? String.Empty : (string)dr["DepartmentName"];
                    cnvp_admin.AdminNo = dr["AdminNo"] is DBNull ? String.Empty : (string)dr["AdminNo"];
                    cnvp_admin.AdminText = dr["AdminText"] is DBNull ? String.Empty : (string)dr["AdminText"];
                    list.Add(cnvp_admin);
                }
            }
            return list;
        }

        public List<Model.CNVP_Admin> QurryListByAdminName(string AdminName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[AdminPass],[LastLoginIp],[LastLoginTime],[LoginNum],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText],[RoleID] from " + Prefix + "Admin");
            sql.Append(" where [AdminName] = @AdminName");
            SqlParameter[] pra ={
                               new SqlParameter("@AdminName",AdminName)
                               };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_Admin> list = new List<Model.CNVP_Admin>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Admin cnvp_admin = new Model.CNVP_Admin();
                    cnvp_admin.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_admin.AdminPass = dr["AdminPass"] is DBNull ? String.Empty : (string)dr["AdminPass"];
                    cnvp_admin.LastLoginIp = dr["LastLoginIp"] is DBNull ? String.Empty : (string)dr["LastLoginIp"];
                    cnvp_admin.LastLoginTime = dr["LastLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastLoginTime"];
                    cnvp_admin.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                    cnvp_admin.IsLock = dr["IsLock"] is DBNull ? (byte)0 : (byte)dr["IsLock"];
                    cnvp_admin.IsAdmin = dr["IsAdmin"] is DBNull ? (byte)0 : (byte)dr["IsAdmin"];
                    cnvp_admin.DepartmentName = dr["DepartmentName"] is DBNull ? String.Empty : (string)dr["DepartmentName"];
                    cnvp_admin.AdminNo = dr["AdminNo"] is DBNull ? String.Empty : (string)dr["AdminNo"];
                    cnvp_admin.AdminText = dr["AdminText"] is DBNull ? String.Empty : (string)dr["AdminText"];
                    cnvp_admin.RoleID = dr["RoleID"] is DBNull ? 0 : (int)dr["RoleID"];
                    list.Add(cnvp_admin);
                }
            }
            return list;

        }

        public Model.CNVP_Admin cust_LoadByAdminName(string AdminName)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[AdminPass],[LastLoginIp],[LastLoginTime],[LoginNum],[IsLock],[IsAdmin],[DepartmentName],[AdminNo],[AdminText],[RoleID] from " + Prefix + "Admin");
            sql.Append(" where [AdminName] = @AdminName");
            SqlParameter[] pra = {
                                 new SqlParameter("@AdminName",AdminName)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            if (DT.Rows.Count > 0)
            {
                Model.CNVP_Admin cnvp_admin = new Model.CNVP_Admin();
                DataRow dr = DT.Rows[0];
                cnvp_admin.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                cnvp_admin.AdminPass = dr["AdminPass"] is DBNull ? String.Empty : (string)dr["AdminPass"];
                cnvp_admin.LastLoginIp = dr["LastLoginIp"] is DBNull ? String.Empty : (string)dr["LastLoginIp"];
                cnvp_admin.LastLoginTime = dr["LastLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["LastLoginTime"];
                cnvp_admin.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                cnvp_admin.IsLock = dr["IsLock"] is DBNull ? (byte)0 : (byte)dr["IsLock"];
                cnvp_admin.IsAdmin = dr["IsAdmin"] is DBNull ? (byte)0 : (byte)dr["IsAdmin"];
                cnvp_admin.DepartmentName = dr["DepartmentName"] is DBNull ? String.Empty : (string)dr["DepartmentName"];
                cnvp_admin.AdminNo = dr["AdminNo"] is DBNull ? String.Empty : (string)dr["AdminNo"];
                cnvp_admin.AdminText = dr["AdminText"] is DBNull ? String.Empty : (string)dr["AdminText"];
                cnvp_admin.RoleID = dr["RoleID"] is DBNull ? 0 : (int)dr["RoleID"];

                return cnvp_admin;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 判断是否存在旧密码
        /// </summary>
        public int IsOldPwd(string OldPwd, string AdminName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Count([ID]) from " + Prefix + "Admin");
            sql.Append(" Where [AdminPass]=@OldPwd and AdminName = @AdminName");
            SqlParameter[] pra = {
                                 new SqlParameter("@OldPwd",OldPwd),
                                  new SqlParameter("@AdminName",AdminName)
                                    };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        /// <summary>
        /// 旧密码存在，更新密码
        /// </summary>
        public int UpdatePwd(string AdminPass, string AdminName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Update " + Prefix + "Admin set [AdminPass] = @AdminPass");
            sql.Append(" where  AdminName = @AdminName");
            SqlParameter[] pra = {
                                 new SqlParameter("@AdminPass",AdminPass),
                                  new SqlParameter("@AdminName",AdminName)
                                    };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }
    }
}

